#!/usr/bin/perl
use warnings;
use strict;

use Tk;
use Tk::NoteBook;
use Tk::ProgressBar;
use Tk::DateEntry;
use Tk::LabFrame;
use Switch;
use Tk::DirSelect;
use Tk::JComboBox;
use Tk::PathEntry::Dialog qw(as_default);
use Time::Local;
use Cwd;
use DateTime;
use Time::SoFar qw( runtime runinterval figuretimes );
use Win32::FileOp qw(ShellExecute);
use DBI;
use GetSummary;
use GetSummaryDB;
use utility;
# some common utility for this project
my $util = new utility;


my $SW_Version = 'R1-0-2a';
my $SW_Author  = 'Roland Yan';

# $util->logmsg($output_text, "Software Ver:	$SW_Version ");
# $util->logmsg($output_text, "Author:		$SW_Author ");

# --- Global variable for ComboBox in Matching Criteria Frame
my @StationTypeList;
my @StationSubTypeList;
my @StationIDList;
my @ProuctIDList;
my @BOMList;
my @FWList;
my @TestSoftwareList;

# query condition
my %filter;
my $IsDB_Used;

# Global variable for data source radiobutton.
my $DatasourceType;

# Globel variable for default selection in list
my $defaultSelection;
my $workingdir = getcwd;

my $mw = MainWindow->new();
$mw->geometry("800x600");
$mw->title( "Koala data summary tool" . "-$SW_Version" );

my $main_menu = $mw->Menu();
$mw->configure( -menu => $main_menu );
my $file_menu =
  $main_menu->cascade( -label => "File", -underline => 0, -tearoff => 0 );
$file_menu->command( -label => "File", -underline => 0 );
$file_menu->command(
	-label     => "Exit",
	-underline => 0,
	-command   => sub { exit }
);

my $help_menu =
  $main_menu->cascade( -label => "Help", -underline => 0, -tearoff => 0 );
$help_menu->command(
	-label     => "Help Content",
	-underline => 0,
	-command   => \&display_message
);

$help_menu->command(
	-label     => "About",
	-underline => 0,
	-command   => \&show_about
);

#$main_menu->command(-label=>"Help", -underline => 0,
#                    -command=>sub{$mw->messageBox(-message=>"Help is not available yet", -type => "ok")});

my $book = $mw->NoteBook()->pack( -fill => 'both', -expand => 1 );

# carete tabs
my $summary_tab =
  $book->add( "Sheet 1", -label => "Summary", -createcmd => \&getStartTime );
my $instruction_tab = $book->add(
	"Sheet 2",
	-label    => "Instruction",
	-raisecmd => \&getCurrentTime
);

# $quit_tab = 		$book->add( "Sheet 3", -label=>"Quit", -state=>'disabled' );
# my $quit_tab = 		$book->add( "Sheet 3", -label=>"Quit");

my $searchdir;
my $opdir;
my $outputfilename;

# my $rowqty    = 8;
# my $columnqty = 6;

# Summary tab confugration ---------------------------------------------------
my $dir_frame =
  $summary_tab->LabFrame( -label => "Choose data source and output location" )
  ->pack( -fill => "x", -side => "top" );

my $datasource_Option_frame =
  $dir_frame->LabFrame( -label => "Data souce Option" )
  ->pack( -expand => 1, -fill => "x", -side => "left" );

my $datasource_RadiobuttonFrame =
  $datasource_Option_frame->Frame()->pack( -fill => "x", -side => "left" );

my $csvFileRadioButton = $datasource_RadiobuttonFrame->Radiobutton(
	-text     => "CSV Files",
	-variable => \$DatasourceType,
	-value    => "csv",
	-command  => \&GetInitChoicesForCriteriaJComboBox
)->pack( -anchor => 'w' );

$datasource_RadiobuttonFrame->Radiobutton(
	-text     => "Factory data in Suzhou",
	-variable => \$DatasourceType,
	-value    => "FactoryDataInSuzhou",
	-command  => \&GetInitChoicesForCriteriaJComboBox
)->pack( -anchor => 'w' );

# $datasource_RadiobuttonFrame->Radiobutton(
#	-text     => "SQL Factory Data",
#	-variable => \$DatasourceType,
#	-value    => "SQL_Factory",
#	-command  => \&GetInitChoicesForCriteriaJComboBox
#)->pack( -anchor => 'w' );

$datasource_RadiobuttonFrame->Radiobutton(
	-text     => "Lab data in Richmod",
	-variable => \$DatasourceType,
	-value    => "LabDataInRichmond",
	-command  => \&GetInitChoicesForCriteriaJComboBox
)->pack( -anchor => 'w' );

my $datasource_frame =
  $datasource_Option_frame->Frame()
  ->pack( -expand => 1, -fill => "x", -side => "left" );

my $csv_frame =
  $datasource_frame->Frame()
  ->pack( -expand => 1, -fill => "x", -side => "top" );

# --- choose directory -------------------
$searchdir = 'c:/tmp';
my $searchpath = $csv_frame->PathEntry(
	-textvariable => \$searchdir,
	-autocomplete => 1,

	#-selectcmd    => sub { warn "The pathname is $searchdir\n" },
	#-selectcmd    => \initilizeVariables(),
	#-width        => 35,
)->pack( -expand => 1, -fill => "x", -side => "left" );

my $getpathbutt = $csv_frame->Button(
	-text    => "Search Dir",
	-command => sub { $searchdir = getstartsearchpath( $mw, 'C:\tmp' ) }
)->pack( -fill => "x", -side => "right" );

my $SQL_Factory_sz = $datasource_frame->Entry(
	-textvariable => "cnszu-app01, cnszu-app02",
	-state        => "readonly",
	-width        => 0
)->pack( -expand => 1, -fill => "x", -side => "top" );

my $SQL_Factory = $datasource_frame->Entry(
	-textvariable => "carmd-app28",
	-state        => "readonly",
	-width        => 0
)->pack( -expand => 1, -fill => "x", -side => "top" );

# my $SQL_Lab = $datasource_frame->Entry(
#	-textvariable => "carmd-app28",
#	-state        => "readonly",
#	-width        => 0
# )->pack( -expand => 1, -fill => "x", -side => "top" );

#my $cvsDatatype = $datasource_frame->Entry(
#	-textvariable => "Result file path will show here!",
#	-state        => "readonly",
#	-width        => 0
#)->pack(-fill=>"x", -side => "left");

# Get the user desktop path and set the output folder
my $homepath = $ENV{'USERPROFILE'};
$homepath =~ s/\\/\//g;
my $desktop_path = "$homepath/Desktop";
$opdir = $desktop_path . '/summaryoutput';

my $ouputpath_frame =
  $dir_frame->LabFrame( -label => "Output Folder" )
  ->pack( -expand => 1, -fill => "x", -side => "left" );

my $ouputpath = $ouputpath_frame->PathEntry(
	-textvariable => \$opdir,
	-autocomplete => 1,

	#-selectcmd    => sub { warn "The pathname is $opdir\n" },
	#-width        => 35,
)->pack( -expand => 1, -fill => "x", -side => "left" );

##my $getoutputpathbutt=$dir_frame-> Button(-text => "Output Dir",
##									-command =>\&getoutputdir)
##									->pack(-side=>"left",-expand=>1);

my $getoutputpathbutt = $ouputpath_frame->Button(
	-text    => "Choose output folder",
	-command => sub { chooseoutputfolder( $mw, $opdir ) }
)->pack( -side => "left" );

# ---
my $criteria_frame =
  $summary_tab->LabFrame( -label => "Matching Criteria" )
  ->pack( -fill => "x", -side => "top" );

# station type.
my $stationtype_frame =
  $criteria_frame->LabFrame( -label => "Station Type" )
  ->pack( -expand => 1, -fill => "x", -side => "left" );
my $stntypelist_JComboBox = $stationtype_frame->JComboBox(
	-entrybackground => 'white',
	-mode            => 'editable',
	-relief          => 'sunken',
	-choices         => \@StationTypeList,
	-selectcommand   => \&UpdateJComboBoxList
)->pack( -expand => 1, -fill => "x", -side => "left" );



# station subtype.
my $stationsubtype_frame =
  $criteria_frame->LabFrame( -label => "Station SubType" )
  ->pack( -expand => 1, -fill => "x", -side => "left" );
my $stnsubtypelist_JComboBox = $stationsubtype_frame->JComboBox(
	-entrybackground => 'white',
	-mode            => 'editable',
	-relief          => 'sunken',
	-choices         => \@StationSubTypeList,
	-selectcommand   => \&UpdateJComboBoxList
)->pack( -expand => 1, -fill => "x", -side => "left" );


# --- Station ID
my $stationID_frame =
  $criteria_frame->LabFrame( -label => "Station ID" )
  ->pack( -expand => 1, -fill => "x", -side => "left" );
my $stnlist_JComboBox = $stationID_frame->JComboBox(
	-entrybackground => 'white',
	-mode            => 'editable',
	-relief          => 'sunken',
	-choices         => \@StationIDList,
	-selectcommand   => \&UpdateJComboBoxList
)->pack( -expand => 1, -fill => "x", -side => "left" );

# --- Product model
my $product_id_frame =
  $criteria_frame->LabFrame( -label => "Product Model" )
  ->pack( -expand => 1, -fill => "x", -side => "left" );
my $prodidlist_JComboBox = $product_id_frame->JComboBox(
	-entrybackground => 'white',
	-mode            => 'editable',
	-relief          => 'sunken',
	-choices         => \@ProuctIDList,
	-selectcommand   => \&UpdateJComboBoxList
)->pack( -fill => "x" );

my $product_BOM =
  $criteria_frame->LabFrame( -label => "Product BOM" )
  ->pack( -expand => 1, -fill => "x", -side => "left" );
my $prodbomlist_JComboBox = $product_BOM->JComboBox(
	-entrybackground => 'white',
	-mode            => 'editable',
	-relief          => 'sunken',
	-choices         => \@BOMList,
	-selectcommand   => \&UpdateJComboBoxList
)->pack( -fill => "x" );

my $product_FW_version =
  $criteria_frame->LabFrame( -label => "Product FW" )
  ->pack( -expand => 1, -fill => "x", -side => "left" );
my $prodfwlist_JComboBox = $product_FW_version->JComboBox(
	-entrybackground => 'white',
	-mode            => 'editable',
	-relief          => 'sunken',
	-choices         => \@FWList,
	-selectcommand   => \&UpdateJComboBoxList
)->pack( -fill => "x" );

my $testcode_FW_version =
  $criteria_frame->LabFrame( -label => "Test Software" )
  ->pack( -expand => 1, -fill => "x", -side => "left" );
my $testcodeRevlist_JComboBox = $testcode_FW_version->JComboBox(
	-entrybackground => 'white',
	-mode            => 'editable',
	-relief          => 'sunken',
	-choices         => \@TestSoftwareList,
	-selectcommand   => \&UpdateJComboBoxList
)->pack( -expand => 1, -fill => "x" );

# ---
my $date_entry_frame =
  $summary_tab->LabFrame( -label => "Misc Setting" )->pack( -side => "top" );

my $radio_frame = $date_entry_frame->LabFrame(
	-label     => "Generate Dist Graph",
	-labelside => "acrosstop"
)->pack( -fill => "x", -side => "left" );

my $gen_dis_graph;
my $Yes_Button = $radio_frame->Radiobutton(
	-text     => "Yes",
	-value    => "Yes",
	-variable => \$gen_dis_graph
)->pack( -fill => "x", -side => "left" );
my $No_Button = $radio_frame->Radiobutton(
	-text     => "No",
	-value    => "No",
	-variable => \$gen_dis_graph
)->pack( -fill => "x", -side => "left" );


my $date_start_frame = $date_entry_frame->LabFrame(
	-label     => "Start Date",
	-labelside => "acrosstop"
)->pack( -fill => "x", -side => "left" );

my $dt_begining = DateTime->new(
      year      => 2003,
      month     => 03,
      day       => 25,
      hour      => 0,
      minute    => 0,
      second    => 0 );

my $date_start   = $date_start_frame->DateEntry(
	-width        => 11,
	-dateformat   => 4,
	-textvariable => $dt_begining->ymd
)->pack( -fill => "x", -side => "left" );

my $date_end_frame = $date_entry_frame->LabFrame(
	-label     => "End Date",
	-labelside => "acrosstop"
)->pack( -fill => "x", -side => "left" );

my $dt_now		= DateTime->now;    # same as ( epoch => time() )
my $enddefault	= $dt_now->ymd;

my $date_end   	= $date_end_frame->DateEntry(
	-width        => 11,
	-dateformat   => 4,
	-textvariable => $enddefault
)->pack( -fill => "x", -side => "left" );

my $execution_but = $date_entry_frame->Button(
	-text    => "Run",
	-command => \&get_report
)->pack( -side => "left" );

# --- output file path and file name
my $output_frame = $summary_tab->LabFrame(
	-label     => "Summary file path and name",
	-labelside => "acrosstop"
)->pack( -side => "top" );

my $output_file = $output_frame->Entry(
	-textvariable => "Result file path will show here!",
	-state        => "readonly",
	-width        => 0
)->pack( -expand => 1, -fill => "x", -side => "left" );

my $openfile_but = $output_frame->Button(
	-text    => "Open File",
	-state   => "normal",
	-command => \&open_in_web_browser
)->pack( -fill => "x", -side => "right" );

# Display Canvas
#my $canvas = $summary_tab->Canvas(-relief=>"sunken", -background=>"gray")
#	->pack( -expand=>1, -fill=>"both");
#my $text = $canvas->createText(10, 10, -font=>'big', -text => 'Canvas example');

# 2nd tab
#$instruction_tab->Label( -textvariable=>\$raisetime )->pack(-expand=>1);

#  3rd tab
#	$quit_tab->Button( -text=>'Quit', -command=>sub{ exit; } )->pack(-expand=>1);

# text message
#my $text_frame = $summary_tab->Frame()
#  							->pack( -expand => 1, -side => "top", -fill=>"both");

#my $output_scroll = $text_frame->Scrollbar();
#my $output_text   = $text_frame->Text(
#	-yscrollcommand => [ 'set', $output_scroll ]#,
#	#-height         => 10
#);


my $text_frame = $summary_tab	->LabFrame(-label=>"Log Window")
  								->pack(-expand=>1, -fill=>"both");

my $output_text = $text_frame->Scrolled('Text',
				-height => '1',
				-width => '1',
				-scrollbars => 'osoe')->pack(-expand=>1, -fill=>"both");

# show progress
my $bar2 = $mw->ProgressBar(
	-padx        => 2,
	-pady        => 2,
	-borderwidth => 2,
	-length      => 100,
	-from        => 0,
	-to          => 100,
	-blocks      => 10,
	-colors      => [ 0, 'green', 50, 'yellow', 80, 'red' ],

	-troughcolor => '#BFEFFF',
	-colors      => [ 0, '#104E8B' ]
)->pack();
$bar2->value(30);

SetDetaultStatus();

MainLoop;

######################################################################################
######################################################################################
sub SetDetaultStatus {

	# After initialization, set the default data source to csv file.
	# Set cvs file radionbutton as default
	$csvFileRadioButton->select();
	GetInitChoicesForCriteriaJComboBox();

	# Set Graph to No.
	$No_Button->select();    # default setting. no graph will be generated.

}

sub getStartTime {
	my $starttime = "Started at " . localtime;
}

sub getCurrentTime {
	my $raisetime = " Last raised at " . localtime;
	$book->pageconfigure( "Sheet 3", -state => 'normal' );
}

sub display_message {
	my ($msg) = @_;

	#my $lname = $last_name->get();
	#if ($lname eq "") { $lname = "No Name"; }
	#my $output = "Hello $stat. $lname!\nI like the following too!";

	#$output_text->delete('0.0', 'end');
	#$output_text->insert("end", $msg);
}

sub show_about {
	my $help_win = $mw->Toplevel;
	$help_win->geometry("400x100");
	$help_win->title("About Koala");

	my $help_msg = "Koala is designed by Roland, a member of 
	SWI manufactur test team.
	The goal is to make generating test summary report easier, faster and accurate.
	Have a question? Drop a email: ryan\@sierrawireless.com";
	
	$help_win->Label( -text => $help_msg )->pack();
	$help_win->Button( -text => "Ok", -command => [ $help_win => 'destroy' ] )
	  ->pack();
}

sub open_in_web_browser {
	$util->logmsg($output_text,"open summary file in browser... \n");
	$util->logmsg($output_text,"\$outputfilename: $outputfilename  \n");

	ShellExecute($outputfilename);
}

sub get_report {
	my $elapsed = runtime();
	if ( 1 == $IsDB_Used ) {
		get_summary_DB();
	}
	else {
		get_summary();
	}
	
	my $sincethen = runinterval(1);
	$util->logmsg($output_text,"=== Getting summary took: $sincethen seconds \n");
}

sub get_summary_DB {

	my $obj        = new GetSummaryDB;
	# Clearn up output file paht field
	$output_file->configure( -textvariable => "" );

	# Disable 'Open' button until file is ready
	$openfile_but->configure( -state => 'disable' );

	# Get the output file path
	my $outputdir = $ouputpath->get();
	$util->logmsg($output_text,"output directory: $outputdir");

	# Make up the output file path
	$outputdir =
	    $outputdir . "/"
	  . $filter{"stnType"} . "_"
	  . $filter{"stnSubType"} . "_"
	  . $filter{"prodID"} . "_"
	  . $filter{"prodBOM"} . "_"
	  . $filter{"prodFW"} . "_"
	  . $filter{"testATEVer"};
	  
	$outputdir =~ s/%/x/g;
	$util->logmsg($output_text,"output directory: $outputdir");

	# Remove the output folder if exist
	if ( -d $outputdir ) {
		$util->logmsg($output_text,"removing directory: $outputdir");
		my @args = ( "rmdir", "$outputdir", "/s", "/q" );
		system(@args);
	}

	# Make up the output file name
	$outputfilename = $outputdir . "/" . "main.html";
	$outputfilename =~ s/\\/\//g;

	$util->logmsg($output_text,"\$outputfilename: $outputfilename");
	# print "\$outputfilename: $outputfilename";
	$util->logmsg($output_text,"Output file: $outputfilename");
	$util->logmsg($output_text,"generating summary...");
		
	Fillup_filter();
	my $Return = $obj->GetSummaryDB(
		# Query condition
		\%filter,
		\$gen_dis_graph,      # if the distribution graphs show
		#
		\$DatasourceType,
		\$workingdir,         # Current working directory
		\$outputfilename,
		$output_text );

	showResult($Return);
}

sub get_summary {

	my $WorkingDir = "C:/carmd-cvs02/Koala";
	my $obj        = new GetSummary;

	$output_file->configure( -textvariable => "" );
	$openfile_but->configure( -state => 'disable' );
	$util->logmsg($output_text,"\$outputfilename: $outputfilename  \n");

	my $searchdirectory = $searchpath->get();
	$util->logmsg($output_text," seatching directory: $searchdirectory \n");
	my $outputdir = $ouputpath->get();
	$util->logmsg($output_text,"output directory: $outputdir \n");

	my $stationtype = $stntypelist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"station type: $stationtype \n");
	my $stationID = $stnlist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"station ID: $stationID \n");
	my $productID = $prodidlist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"product ID: $productID \n");
	my $productBOM = $prodbomlist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"BOM revision: $productBOM \n");
	my $productFW = $prodfwlist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"FW version: $productFW \n");
	my $testcodeversion = $testcodeRevlist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"Mfg Test code version: $testcodeversion \n");
	my $startdate = $date_start->get();
	$util->logmsg($output_text,"search from: $startdate \n");
	my $enddate = $date_end->get();
	$util->logmsg($output_text,"search to: $enddate \n");

	$outputdir =
	    $outputdir . "/"
	  . $stationtype . "_"
	  . $productID . "_"
	  . $productBOM . "_"
	  . $productFW . "_"
	  . $testcodeversion;
	$outputdir =~ s/\.\*/x/g;

	my @args = ( "rmdir", "$outputdir", "/s", "/q" );

	system(@args);
	$outputfilename = $outputdir . "/" . "main.html";
	$outputfilename =~ s/\\/\//g;

	$util->logmsg($output_text,"\$outputfilename: $outputfilename \n");
	$util->logmsg($output_text,"Output file: $outputfilename \n");
	$util->logmsg($output_text,"generating summary...\n");

	my $Return = $obj->GetSummary(
		$WorkingDir,         # Current working directory
		$outputdir,          # HTML output directory.
		$searchdirectory,    # the root searching diectory
		$stationtype,        # test station type
		$stationID,          # Station ID.
		$productID,          # DUT product ID / FSN?
		$productBOM,         # DUT BOM revision
		$productFW,          # DUT FW version
		$testcodeversion,    # mfg test sw revision
		$startdate,          # start of date
		$enddate,            # end of date
		$gen_dis_graph,      # if the distribution graphs show
		$outputfilename,
		$output_text         # Msg log windows
	);

	showResult($Return);
}

sub showResult{
	my $ret = shift;

	if ( 1 == $ret ) {
		$util->logmsg($output_text,"The \$outputfilename: $outputfilename \n");

		$util->logmsg($output_text,"summary report generated successfuly. \n");

		$output_file->configure( -textvariable => $outputfilename );
		$openfile_but->configure( -state => 'active' );
	}
	else {
		$util->logmsg($output_text,"failed to generate summary report. \n");
	}	
}

sub getsearchingdir {
	my $ds  = $dir_frame->DirSelect();
	my $dir = $ds->Show();

	$searchpath->delete( 0, "end" );
	$searchpath->insert( 0, $dir );
	$searchdir = $searchpath->get();
	$util->logmsg($output_text,"Will search following directory: $searchdir \n");
}

sub getstartsearchpath {
	my $top         = shift;
	my $initialpath = shift;

	$util->logmsg($output_text,"\$initialpath: $initialpath \n");
	my $dir = $top->chooseDirectory(
		-title      => 'Choose the start search path',
		-initialdir => $initialpath,
		-parent     => $top
	);

	return $dir;
}

sub getoutputdir {

	my $ds  = $ouputpath->DirSelect();    #$searchpath
	my $dir = $ds->Show();

	$ouputpath->delete( 0, "end" );
	$ouputpath->insert( 0, $dir );

	$opdir = $ouputpath->get();
	$util->logmsg($output_text,"Output will be saved to following directory: $opdir \n");
}

sub chooseoutputfolder {

	my $top         = shift;
	my $initialpath = shift;

	#$output_text->insert( "end", "\$initialpath: $initialpath \n" );
	$util->logmsg($output_text,"\$initialpath: $initialpath \n");
	my $dir = $top->chooseDirectory(
		-title      => 'Choose the start search path',
		-initialdir => $initialpath,
		-parent     => $top
	);
}

sub run_query {
	my $dbh      = shift;
	my $sql_stmt = shift;

	# selectall_arrayref returns a bidimensional array: columns by rows
	return $dbh->selectall_arrayref($sql_stmt);
}

sub GetInitChoicesForCriteriaJComboBox {
	my $WidgetSelectCommandQuitStatus;

	# $DatasourceType is global variable when radio button were created.
	$util->logmsg($output_text,"Datasource type: $DatasourceType");
	$util->logmsg($output_text,"Initialize variable for $DatasourceType");
	
	my @cb_array = (
		$stntypelist_JComboBox, 
		$stnsubtypelist_JComboBox,
		$stnlist_JComboBox,
		$prodidlist_JComboBox,  
		$prodbomlist_JComboBox,
		$prodfwlist_JComboBox,  
		$testcodeRevlist_JComboBox
	);
	
	switch ($DatasourceType) {
		case /^csv$/ {
			# make the station subtype column invisible.
			$stationsubtype_frame->packForget;
			$WidgetSelectCommandQuitStatus = 0;
			$IsDB_Used                     = 0;
			
			$defaultSelection = ".*";
			SetFirstValueForLists($defaultSelection);
			SetDefaultWidgetsSlection($WidgetSelectCommandQuitStatus, \@cb_array);

			UpdateInitVariablesForCVS();
		}
		case /^FactoryDataInSuzhou$/ {
			# make the station subtype column visible.
			$stationsubtype_frame->pack(-after	=> $stationtype_frame,	-side => 'left');
			$stationsubtype_frame->pack(-before => $stationID_frame,	-side => 'left');
			
			$defaultSelection              = "%";
			$WidgetSelectCommandQuitStatus = 1;
			$IsDB_Used                     = 1;

			SetFirstValueForLists($defaultSelection);
			SetDefaultWidgetsSlection($WidgetSelectCommandQuitStatus, \@cb_array);

			UpdateJComboBoxList();
		}
		case /^LabDataInRichmond$/ {
			# make the station subtype column visible.
			$stationsubtype_frame->pack(-after 	=> $stationtype_frame,	-side => 'left');
			$stationsubtype_frame->pack(-before => $stationID_frame,	-side => 'left');
			
			$defaultSelection              = "%";
			$WidgetSelectCommandQuitStatus = 1;
			$IsDB_Used                     = 1;

			SetFirstValueForLists($defaultSelection);
			SetDefaultWidgetsSlection($WidgetSelectCommandQuitStatus, \@cb_array);

			UpdateJComboBoxList();
		}
		else {
			$util->logmsg($output_text,"Invalide data source type");
			return 0;
		}
	}
}

sub SetWidgetSelectCommandCallbackStatus {
	my $status   = shift;
	my @cb_array = (
		$stntypelist_JComboBox, $stnlist_JComboBox,
		$prodidlist_JComboBox,  $prodbomlist_JComboBox,
		$prodfwlist_JComboBox,  $testcodeRevlist_JComboBox
	);

	foreach (@cb_array) {
		if ( $status == 0 ) {
			selectCommandOff($_);
		}
		else {
			selectCommandOn($_);
		}
	}
}

sub SetFirstValueForLists {
	my $default = shift;
	@StationTypeList  	= ($default);
	@StationSubTypeList	= ($default);
	@StationIDList    	= ($default);
	@ProuctIDList     	= ($default);
	@BOMList          	= ($default);
	@FWList           	= ($default);
	@TestSoftwareList 	= ($default);
}

sub UpdateInitVariablesForCVS {
	# Created a default list for ComboBox
	push( @StationTypeList, "BFT", "RFCAL", "RIL", "SFT", "RSFT" );
	push( @StationIDList, 	"SWI_CAL_L_1", "SWI_CAL_L_4", "FLD_CAL_L_40" );
	push( @ProuctIDList, 	"D68", "D90", "D89", "F92", "D57332800081010" );
	push( @BOMList, 		"10",         "20",         "22",         "0A" );
	push( @FWList,  		"M1_0_0_0AP", "K1_0_0_0AP", "J1_0_0_0AP", "F1_0_0_0AP" );
	push( @TestSoftwareList,"R1-0-0", "R1-2-2", "R1-1-5h", "R0-0-7" );
	
	# Update the date range based on sql query results.
	$date_start	->configure(-textvariable => $dt_begining->ymd);
	$date_end	->configure(-textvariable => $dt_now->ymd);
	
}

sub SetDefaultWidgetsSlection {
	my $status   = shift;
	my $CB_arrayRef = shift;

	foreach (@{$CB_arrayRef}) {
		selectCommandOff($_);
		$_->setSelectedIndex(0);
		if ( $status == 1 ) {
			selectCommandOn($_);
		}
		elsif ( $status == 0 ) {
			selectCommandOff($_);
		}
		else {
			$util->logmsg($output_text,
				"Widget selectcommand status are not set correctly" );
		}
	}
}

sub selectCommandOn {
	my $cb = shift;
	$cb->configure( -selectcommand => \&UpdateJComboBoxList );
}

sub selectCommandOff {
	my $cb = shift;
	$cb->configure( -selectcommand => undef );
}

sub GetSelectionList {
	my $dbhandle	= shift;
	my $DB_ColumnRef= shift;

	my @arraytmp = GetList($dbhandle, $DB_ColumnRef);
	# Insert the default selection charactor to 1st place.
	unshift( @arraytmp, $defaultSelection );
	# Sort the selections.
	@arraytmp = sort(@arraytmp);
	return @arraytmp;
}

sub UpdateJComboBoxList {
	# When hit this routine, assume that the ComboBox has all their own selection
	# already. These selection will be used to get the selection list from DB.
	# Get all selection list based on current user selection from ComboBoxes.
	my ($DBServerName, $DBname, $DBusername, $DBpassword)	= 
				$util->get_DB_connectionInfo (\$DatasourceType, \"main");
	
	unless (defined $DBServerName && defined $DBname && defined $DBusername && defined $DBpassword) {
		$util->logmsg($output_text, "Failed to get DB related information");
		return undef;
	}

	my $dbh = $util->connect_to_db_server_mssql( $DBServerName, $DBname, $DBusername, $DBpassword );

	my @StationTypeList_init 	= GetSelectionList($dbh, \"G_Station_Type" ); 
	my @StationSubTypeList_init = GetSelectionList($dbh, \"G_Station_SubType" );
	my @StationIDList_init   	= GetSelectionList($dbh, \"G_Station_Number" );
	my @ProuctIDList_init    	= GetSelectionList($dbh, \"G_DUT_Type" );
	my @BOMList_init         	= GetSelectionList($dbh, \"G_FSN_BOM" );
	my @FWList_init          	= GetSelectionList($dbh, \"G_Firmware_Version" );
	my @TestCodeList_init    	= GetSelectionList($dbh, \"G_App_Software_Version" );
	
	#	# Handler of ComboBox,			# Array holds list			# Updated global list
	my @JComboBoxAoA = (
		[ $stntypelist_JComboBox, 		\@StationTypeList_init, 	\@StationTypeList ],
		[ $stnsubtypelist_JComboBox,	\@StationSubTypeList_init, 	\@StationSubTypeList ],
		[ $stnlist_JComboBox,     		\@StationIDList_init,   	\@StationIDList ],
		[ $prodidlist_JComboBox,  		\@ProuctIDList_init,    	\@ProuctIDList ],
		[ $prodbomlist_JComboBox, 		\@BOMList_init,         	\@BOMList ],
		[ $prodfwlist_JComboBox,  		\@FWList_init,          	\@FWList ],
		[ $testcodeRevlist_JComboBox,	\@TestCodeList_init, 		\@TestSoftwareList ]
	);

	# Loop all ComboBox;
	my $CB_REFERENCE_INDEX           = 0;
	my $CB_SELECTIONLIST_INDEX       = 1;
	my $CB_GLOBALSELECTIONLIST_INDEX = 2;

	foreach (@JComboBoxAoA) {
		# 
		my $currCB            = $_->[$CB_REFERENCE_INDEX];
		my @currSelectionList = @{ $_->[$CB_SELECTIONLIST_INDEX] };
		my $currSelection = $currCB->getSelectedValue();

		# Disable SelectCommand call back.
		selectCommandOff($currCB);

		# Update the global SelectionList array
		@{ $_->[$CB_GLOBALSELECTIONLIST_INDEX] } =
		  @{ $_->[$CB_SELECTIONLIST_INDEX] };

		my $idx = List::MoreUtils::first_index { $_ eq $currSelection } @{ $_->[$CB_GLOBALSELECTIONLIST_INDEX] };
		# if current selection is not in the latested list,
		# it will be set to default.
		if ( $idx >= 0 ) {
			$currCB->setSelectedIndex($idx);
		}
		else {
			$currCB->setSelectedIndex(0);
		}
		# Enable selectCommand call back
		selectCommandOn($currCB);
	}
	
	# Get all filter elements, 
	Fillup_filter();
	# Then query result, determine the date range
	my ($dt_start , $dt_end) = $util->getDateRange($dbh, \%filter);
	$util->logmsg($output_text,"start date: $dt_start->ymd, end date: $dt_end->ymd");
	
	$util->disconnDB($dbh);
	
	# Update the date range based on sql query results.
	$date_start->configure(-textvariable=>$dt_start->ymd);
	$date_end->configure(-textvariable => $dt_end->ymd);
}

sub Fillup_filter {
	# udpate the query filters.
	$filter{'stnType'} = $stntypelist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"station type: $filter{'stnType'}");
		
	$filter{'stnSubType'} = $stnsubtypelist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"station SubType: $filter{'stnSubType'}");
	
	$filter{'stnID'} = $stnlist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"station ID: $filter{'stnID'}");

	$filter{'prodID'} = $prodidlist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"product ID: $filter{'prodID'}");
	
	$filter{'prodBOM'} = $prodbomlist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"BOM revision: $filter{'prodBOM'} ");
	
	$filter{'prodFW'} = $prodfwlist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"FW version: $filter{'prodFW'}");
	
	$filter{'testATEVer'}= $testcodeRevlist_JComboBox->getSelectedValue();
	$util->logmsg($output_text,"Mfg Test code version: $filter{'testATEVer'}");
	
	$filter{'startDate'}= $date_start->get();
	$util->logmsg($output_text,"search from: $filter{'startDate'}");

	$filter{'endDate'} = $date_end->get();
	$util->logmsg($output_text,"search to: $filter{'endDate'}");
	
}

sub GetList {
	my $dbhandle 		= shift;
	my $ColumnNameRef 	= shift;
	my @returnArray;

	# Get  G_Station_SubType list
	my $sqlScript = GenerateSQLScript($ColumnNameRef);
	$util->logmsg($output_text, "script for $ColumnNameRef: $sqlScript");
	my $res = run_query( $dbhandle, $sqlScript );

	foreach ( @{$res} ) {
		push @returnArray, $_->[0];
	}
	return @returnArray;
}

sub GenerateSQLScript {
	my $columnNameRef = shift;

	my $StationType  	= $stntypelist_JComboBox	->getSelectedValue();
	my $StationSubType  = $stnsubtypelist_JComboBox	->getSelectedValue();
	my $StationID    	= $stnlist_JComboBox		->getSelectedValue();
	my $ProductModel 	= $prodidlist_JComboBox		->getSelectedValue();
	my $ProdBOM      	= $prodbomlist_JComboBox	->getSelectedValue();
	my $ProdFW       	= $prodfwlist_JComboBox		->getSelectedValue();
	my $TestCode     	= $testcodeRevlist_JComboBox->getSelectedValue();
	my $startDate 		= $date_start	->get();
	my $endDate			= $date_end		->get();
	
	return sprintf "
	select distinct($$columnNameRef)
	from dbo.TBL_GENERAL_DATA 
	where G_Station_Type 		like '%s'
	and G_Station_SubType		like '%s'
	and G_Station_Number		like '%s'
	and G_DUT_Type				like '%s'
	and G_FSN_BOM				like '%s'
	and G_Firmware_Version		like '%s'
	and G_App_Software_Version	like '%s'
	and DB_Date_Time_Inserted BETWEEN '%s 00:00:00' AND '%s 23:59:59'",
	$StationType,
	$StationSubType,
	$StationID,
	$ProductModel,
	$ProdBOM,
	$ProdFW,
	$TestCode,
	$startDate,
	$endDate;
}

#END {
#
#	# Close the database
#	$dbh->disconnect() if ( ref($dbh) );
#}
